USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetCityXml')
	DROP FUNCTION dbo.GetCityXml
GO


CREATE FUNCTION [dbo].[GetCityXml] (@userID INT) 
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result = '<CITIES><entry id="0" active="1"></entry>'

	select @result = @result + '<entry id="' + CAST([CityNo] AS VARCHAR) + '" active="'+ CAST( ISNULL(c.ISActive,0) AS VARCHAR) +'"><![CDATA[' +  CityName + ']]></entry>'
	FROM TR_CITY as c
	INNER JOIN TR_PARTNER as p
		ON p.PartnerGUID = c.UserGUID
	WHERE 
		p.PartnerNo = @userID
	ORDER BY CityName
	
	SET @result = @result + '</CITIES>'

	return @result

END

-- SELECT dbo.GetCityXml(2)